﻿// <copyright file="ExportForm.TERP.cs" company="BerlinSoft">
// Copyright (c) 2009 All Right Reserved
// </copyright>
// <author>Wernfried Schwenkner</author>
// <email>wernfried@schwenkner.de</email>
// <date>2009-10-21</date>
// <summary>
// Contains the ExportForm for TERP export.
// </summary>

using System;
using System.Data.SqlServerCe;
using System.Globalization;
using System.IO;
using System.Text;
using SenseTimeTracker.Misc;

namespace SenseTimeTracker.Export
{
	/// <summary>
	/// Class implements the TERP export form
	/// </summary>
	public partial class ExportForm
	{
		/// <summary>
		/// Types of export headers
		/// </summary>
		public enum TietoExportHeaderType
		{
			/// <summary>
			/// TERP export header used
			/// </summary>
			TERP,

			/// <summary>
			/// MyTime export headers used
			/// </summary>
			MyTime,
		}

		/// <summary>
		/// Gets the date of the monday fo a given weekday
		/// </summary>
		/// <param name="date">Date to get the day of mondey to get</param>
		/// <returns>Date of the monday</returns>
		private static DateTime DateOfMonday(DateTime date)
		{
			return date.AddDays(-((int)date.DayOfWeek - (int)DayOfWeek.Monday) - (date.DayOfWeek == DayOfWeek.Sunday ? 7 : 0)).Date;
		}

		/// <summary>
		/// Gets the time cards to be exported
		/// </summary>
		/// <param name="dateBegin">Begin date</param>
		/// <param name="dateFinal">Final date</param>
		/// <param name="connection">Connection to database</param>
		/// <returns>The data reader with the records to export</returns>
		private static SqlCeDataReader GetTimeCards(DateTime dateBegin, DateTime dateFinal, SqlCeConnection connection)
		{
			SqlCeCommand command = new SqlCeCommand(
				"SELECT * FROM Clients " +
				"JOIN Projects ON Clients.clientId = Projects.clientId " +
				"JOIN Tasks ON Projects.projectId = Tasks.projectId " +
				"JOIN TimeRecords ON Tasks.taskId = TimeRecords.taskId " +
				"WHERE timeRecordStartTime >= @dateBegin AND timeRecordFinalTime < @dateFinal AND TimeRecords.personId = @personId " +
				"ORDER BY Clients.clientId, Projects.projectId, Tasks.taskNumber, Tasks.taskType, TimeRecords.timeRecordStartTime",
				connection);
			command.Parameters.Add("@dateBegin", dateBegin);
			command.Parameters.Add("@dateFinal", dateFinal);
			command.Parameters.Add("@personId", Persistance.Instance.UserId);
			SqlCeDataReader reader = command.ExecuteReader();
			return reader;
		}

		/// <summary>
		/// Writes the TERP header
		/// </summary>
		/// <param name="writer">Writer to write the header to</param>
		private static void WriteTerpHeader(TextWriter writer)
		{
			writer.WriteLine("START_HEADER,");
			writer.WriteLine("Overroding Approver, \"\"");
			writer.WriteLine("Comments, \"TimeTracker V{0}\"", DatabaseManager.CurrentVersion.ToString(3));
			writer.WriteLine("STOP_HEADER,");
			writer.WriteLine("START_TEMPLATE,,,,,,,,,,,,,,,,,");
			writer.WriteLine("Project,Task,Type,Mon,CommentText,Tue,CommentText,Wed,CommentText,Thu,CommentText,Fri,CommentText,Sat,CommentText,Sun,CommentText,END_COLUMN");
		}

		/// <summary>
		/// Writes the TERP footer
		/// </summary>
		/// <param name="writer">Writer to write the footer to</param>
		private static void WriteTerpFooter(TextWriter writer)
		{
			writer.WriteLine("STOP_TEMPLATE,,,,,,,,,,,,,,,,,");
			writer.WriteLine("###############################");
			writer.WriteLine("ORACLE RESERVED SECTION");
			writer.WriteLine("###############################");
			writer.WriteLine("START_ORACLE,");
			writer.WriteLine("A|PROJECTS|Attribute1|A|PROJECTS|Attribute2|AE|PROJECTS|Attribute3|PROJECTS|Attribute5|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText,");
			writer.WriteLine("2121472,");
			writer.WriteLine("A|APPROVAL|Attribute10|DI|CommentText|,");
			writer.WriteLine("STOP_ORACLE,END");
		}

		/// <summary>
		/// Writes the MyTime header
		/// </summary>
		/// <param name="writer">Writer to write the header to</param>
		private static void WriteMyTimeHeader(TextWriter writer)
		{
			writer.WriteLine("START_HEADER,");
			writer.WriteLine("Overriding Approver,,,,,,,,,,,,,,,,,");
			writer.WriteLine("Comments,,,,,,,,,,,,,,,,,"/*, DatabaseManager.CurrentVersion.ToString(3)*/);
			writer.WriteLine("STOP_HEADER,");
			writer.WriteLine(",,,,,,,,,,,,,,,,,");
			writer.WriteLine("START_TEMPLATE,,,,,,,,,,,,,,,,,");
			writer.WriteLine("Project,Task,Type,Mon,CommentText,Tue,CommentText,Wed,CommentText,Thu,CommentText,Fri,CommentText,Sat,CommentText,Sun,CommentText,END_COLUMN");
		}

		/// <summary>
		/// Writes the MyTime footer
		/// </summary>
		/// <param name="writer">Writer to write the footer to</param>
		private static void WriteMyTimeFooter(TextWriter writer)
		{
			writer.WriteLine(" , , ,,,,,,,,,,,,,,,");
			writer.WriteLine("STOP_TEMPLATE");
			writer.WriteLine("ORACLE RESERVED SECTION");
			writer.WriteLine(",,,,,,,,,,,,,,,,,");
			writer.WriteLine("START_ORACLE,");
			writer.WriteLine("A|PROJECTS|Attribute1|A|PROJECTS|Attribute2|AE|PROJECTS|Attribute3|PROJECTS|Attribute5|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText|D|DI|CommentText,");
			writer.WriteLine("2121472,,,,,,,,,,,,,,,,,");
			writer.WriteLine("A|APPROVAL|Attribute10|DI|CommentText|,,,,,,,,,,,,,,,,,");
			writer.WriteLine("STOP_ORACLE,END");
		}

		/// <summary>
		/// Exports records to TERP
		/// </summary>
		/// <param name="date">Date within the given period to be exported</param>
		/// <param name="exportPeriod">Period to be exported</param>
		/// <param name="tietoExportType">Tieto export type (TERP, MyTime)</param>
		private void ExportTieto(DateTime date, ExportPeriod exportPeriod, TietoExportHeaderType tietoExportType)
		{
			DateTime dateToExport = date;
			switch (exportPeriod)
			{
				case ExportPeriod.Week:
					ExportTieto(dateToExport, tietoExportType);
					break;
				case ExportPeriod.Month:
					dateToExport -= TimeSpan.FromDays(date.Day - 1);
					while (dateToExport.Month == date.Month || DateOfMonday(dateToExport).Month == date.Month)
					{
						ExportTieto(dateToExport, tietoExportType);
						dateToExport += TimeSpan.FromDays(7);
					}

					break;
			}
		}

		/// <summary>
		/// Exports to TERP
		/// </summary>
		/// <param name="date">Date to export</param>
		/// <param name="tietoExportType">Tieto export type (TERP, MyTime)</param>
		private void ExportTieto(DateTime date, TietoExportHeaderType tietoExportType)
		{
			CultureInfo provider = new CultureInfo("en-us");
			DateTime dateBegin = DateOfMonday(date);
			DateTime dateFinal = dateBegin.AddDays(7).Date;
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = GetTimeCards(dateBegin, dateFinal, DatabaseManager.DbConnection);

			TextWriter writer = null;
			DateTime currentDate = DateTime.MinValue;
			string projectNumber = null;
			string taskNumber = null;
			string taskType = null;
			string taskLocation = string.Empty;

			bool firstRecord = true;
			StringBuilder totalDescription = new StringBuilder();
			TimeSpan totalTime = TimeSpan.Zero;
			StringBuilder timeCard = new StringBuilder();
			while (reader.Read())
			{
				if (writer == null)
				{
					switch (tietoExportType)
					{
						case TietoExportHeaderType.TERP:
							writer = new StreamWriter(DetermineFilename(dateBegin, dateFinal, "terp", true));
							WriteTerpHeader(writer);
							break;
						case TietoExportHeaderType.MyTime:
							writer = new StreamWriter(DetermineFilename(dateBegin, dateFinal, "mytm", true));
							WriteMyTimeHeader(writer);
							break;
					}
				}

				string currentProjectNumber = reader["projectNumber"].ToString().Trim();
				string currentTaskNumber = reader["taskNumber"].ToString().Trim();
				string currentTaskType = reader["taskType"].ToString().Trim();
				if (
					projectNumber != currentProjectNumber ||
					taskNumber != currentTaskNumber ||
					taskType != currentTaskType)
				{
					if (!firstRecord)
					{
						if (totalTime != TimeSpan.Zero)
						{
							timeCard.Append(totalTime.TotalHours.ToString(provider));
							totalTime = TimeSpan.Zero;
						}

						timeCard.Append(",");
						if (totalDescription.Length != 0)
						{
							timeCard.Append("\"");
							timeCard.Append(totalDescription);
							timeCard.Append(taskLocation);
							timeCard.Append("\"");
							totalDescription.Length = 0;
						}

						timeCard.Append(",");
						currentDate = currentDate.AddDays(1.0);
						while (currentDate < dateFinal)
						{
							timeCard.Append(",,");
							currentDate = currentDate.AddDays(1.0);
						}

						if (writer != null)
						{
							writer.WriteLine(timeCard.ToString());
						}

						timeCard.Length = 0;
					}

					firstRecord = false;

					projectNumber = currentProjectNumber;
					taskNumber = currentTaskNumber;
					taskType = currentTaskType;

					timeCard.Append(currentProjectNumber);
					timeCard.Append(",");
					timeCard.Append(currentTaskNumber);
					timeCard.Append(",");
					timeCard.Append(currentTaskType);
					timeCard.Append(",");
					currentDate = dateBegin;
				}

				DateTime startTime = (DateTime)reader["timeRecordStartTime"];
				DateTime finalTime = (DateTime)reader["timeRecordFinalTime"];
				while (currentDate < startTime.Date)
				{
					if (totalTime != TimeSpan.Zero)
					{
						timeCard.Append(totalTime.TotalHours.ToString(provider));
						totalTime = TimeSpan.Zero;
					}

					timeCard.Append(",");
					if (totalDescription.Length != 0)
					{
						timeCard.Append("\"");
						timeCard.Append(totalDescription);
						timeCard.Append(taskLocation);
						timeCard.Append("\"");
						totalDescription.Length = 0;
					}

					timeCard.Append(",");
					currentDate = currentDate.AddDays(1.0);
				}

				totalTime += finalTime - startTime;
				string description = reader["description"].ToString().Trim();
				string details = reader["details"].ToString().Trim();
				taskLocation = (bool)reader["taskTerpExportLocation"] ? (reader["taskName"] + " " + reader["taskLocation"]).Trim() : string.Empty;
				if (!string.IsNullOrEmpty(taskLocation))
				{
					taskLocation = ", " + taskLocation;
				}

				if (!string.IsNullOrEmpty(description) && totalDescription.Length != 0)
				{
					totalDescription.Append(", ");
				}

				totalDescription.Append(description);
				if (!string.IsNullOrEmpty(details) && totalDescription.Length != 0)
				{
					totalDescription.Append(", ");
				}

				totalDescription.Append(details);
			}

			DatabaseManager.DbConnection.Close();

			if (currentDate != DateTime.MinValue)
			{
				if (totalTime != TimeSpan.Zero)
				{
					timeCard.Append(totalTime.TotalHours.ToString(provider));
					totalTime = TimeSpan.Zero;
				}

				timeCard.Append(",");
				if (totalDescription.Length != 0)
				{
					timeCard.Append("\"");
					timeCard.Append(totalDescription);
					timeCard.Append(taskLocation);
					timeCard.Append("\"");
					totalDescription.Length = 0;
				}

				timeCard.Append(",");
				currentDate = currentDate.AddDays(1.0);
				while (currentDate < dateFinal)
				{
					timeCard.Append(",,");
					currentDate = currentDate.AddDays(1.0);
				}

				if (writer != null)
				{
					writer.WriteLine(timeCard.ToString());
				}
			}

			if (writer != null)
			{
				switch (tietoExportType)
				{
					case TietoExportHeaderType.TERP:
						WriteTerpFooter(writer);
						break;
					case TietoExportHeaderType.MyTime:
						WriteMyTimeFooter(writer);
						break;
				}

				writer.Close();
			}
		}

		/// <summary>
		/// Determines the file name of the export file from the day to export
		/// </summary>
		/// <param name="dateBegin">Begin date</param>
		/// <param name="dateFinal">Final date</param>
		/// <param name="fileExtension">File extension</param>
		/// <param name="withWeekNumber">Number of the week in the year</param>
		/// <returns>The file name</returns>
		private string DetermineFilename(DateTime dateBegin, DateTime dateFinal, string fileExtension, bool withWeekNumber)
		{
			const string dateFormat = "MMM dd, yyyy";
			if (withWeekNumber)
			{
				return string.Format(
					Environment.GetFolderPath(Environment.SpecialFolder.Personal) + 
					Path.DirectorySeparatorChar + "timecard - KW{0:D2} - {1} - {2}.{3}",
					CalenderWeek(dateBegin),
					dateBegin.ToString(dateFormat),
					dateFinal.AddDays(-1.0).ToString(dateFormat),
					fileExtension);
			}

			return string.Format(
				Environment.GetFolderPath(Environment.SpecialFolder.Personal) +
				Path.DirectorySeparatorChar + "timecard - {0} - {1}.{2}",
				dateBegin.ToString(dateFormat),
				dateFinal.AddDays(-1.0).ToString(dateFormat),
				fileExtension);
		}
	}
}
